﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Resources;
using System.Text.RegularExpressions;

namespace SqlMiM.Common.Sql
{

    public class SqlCeCommands
    {

        #region Const : Describes all Commands

        public const string INSERT_CONFIG = "InsertConfig";
        public const string UPDATE_CONFIG = "UpdateConfig";
        public const string DELETE_CONFIG = "DeleteConfig";
        public const string SELECT_ALL_CONFIGS = "SelectAllConfigs";
        public const string SELECT_CONFIG = "SelectConfig";
        public const string CREATE_CONFIG_TABLE = "CreateConfigTable";
        public const string EXIST_CONFIG_TABLE = "ExistConfigTable";

        #endregion

        /// <summary>
        /// Contains all commands prepared.
        /// </summary>
        private static Dictionary<String, SqlCeCommand> cachedCommands;


        /// <summary>
        /// Static Constructor. Add all SqlCeCommands.
        /// </summary>
        static SqlCeCommands()
        {
            cachedCommands = new Dictionary<string, SqlCeCommand>(20);

        }



        ///// <summary>
        ///// Get SqlCeCommand by its Command name
        ///// </summary>
        //public static SqlCeCommand this[string commandName]
        //{
        //    get
        //    {
        //        if (cachedCommands.ContainsKey(commandName))
        //            return cachedCommands[commandName];

        //        SqlCeCommand co = new SqlCeCommand();

        //        cachedCommands.Add(commandName, co);

        //        return co;
        //    }
        //    set
        //    {
        //        if (value == null)
        //        {
        //            if (cachedCommands.ContainsKey(commandName))
        //                cachedCommands.Remove(commandName);
        //        }
        //        else
        //        {
        //            if (cachedCommands.ContainsKey(commandName))
        //                cachedCommands[commandName] = value;
        //            else
        //                cachedCommands.Add(commandName, value);

        //        }
        //    }

        //}


        #region Create Command

        /// <summary>
        /// Create a command with transaction
        /// </summary>
        public static SqlCeCommand CreateCommand(SqlCePoolConnection sqlCePoolConnection, String commandScript, bool isRessource, params object[] args)
        {
            SqlCeCommand command = null;

            // try to get this command from cache
            if (!cachedCommands.TryGetValue(commandScript, out command))
            {
                if (isRessource)
                {
                    switch (commandScript)
                    {
                        case CREATE_CONFIG_TABLE: command = GetCreateConfigTableCommand(); break;
                        case EXIST_CONFIG_TABLE: command = GetExistConfigTableCommand(); break;
                        case SELECT_ALL_CONFIGS: command = GetSelectAllConfigurationsCommand(); break;
                        case SELECT_CONFIG: command = GetSelectConfigurationCommand(); break;
                        case INSERT_CONFIG: command = GetInsertConfigurationsCommand(); break;
                        case UPDATE_CONFIG: command = GetUpdateConfigurationsCommand(); break;
                        case DELETE_CONFIG: command = GetDeleteConfigurationsCommand(); break;
                    }
                }
                else
                {
                    // Create command
                    command = sqlCePoolConnection.CreateCommand();

                    // set type
                    command.CommandType = CommandType.Text;

                    // set Text
                    command.CommandText = commandScript;
                }

                // Associate with transaction
                if (sqlCePoolConnection.SqlCeTransaction != null) command.Transaction = sqlCePoolConnection.SqlCeTransaction;

                // Set parameters
                SetParameters(commandScript, ref command, args);

                // Add it to cache
                cachedCommands.Add(commandScript, command);
            }

            // Set current connection
            command.Connection = sqlCePoolConnection.SqlCeConnection;

            // Associate arguments
            for (int i = 0; i < command.Parameters.Count; i++)
                command.Parameters[i].Value = args[i];

            // Return command
            return command;
        }

        /// <summary>
        /// Create a command with no params
        /// </summary>
        public static SqlCeCommand CreateCommand(SqlCePoolConnection conn, string commandScript, bool isRessource)
        {
            return CreateCommand(conn, commandScript, isRessource, null);
        }

        /// <summary>
        /// Create a command from ressource
        /// </summary>
        public static SqlCeCommand CreateCommand(SqlCePoolConnection conn, string commandName)
        {
            return CreateCommand(conn, commandName, true, null);
        }

        /// <summary>
        /// Create a list of commands from a long script in ressource
        /// </summary>
        public static List<SqlCeCommand> CreateAllCommands(SqlCePoolConnection conn, string commandScriptsName)
        {
            String scripts = Properties.Resources.ResourceManager.GetString(commandScriptsName);

            if (string.IsNullOrEmpty(scripts))
                throw new ApplicationException(String.Format("In proc. Sql Scripts {0} doesn't exists", commandScriptsName));

            string[] strCommands = ParseScriptToCommands(scripts);

            if (strCommands == null || strCommands.Length == 0) return null;

            List<SqlCeCommand> sqlCeCommands = new List<SqlCeCommand>();

            foreach (string scriptCommand in strCommands)
                sqlCeCommands.Add(CreateCommand(conn, scriptCommand, false));

            return sqlCeCommands;
        }


        #endregion

        /// <summary>
        /// Get all scripts commands to execute
        /// </summary>
        private static string[] ParseScriptToCommands(string strScript)
        {
            return Regex.Split(strScript, "\r\nGO\r\n", RegexOptions.IgnoreCase);
        }


        /// <summary>
        /// Set parameters
        /// </summary>
        private static void SetParameters(String commandName, ref SqlCeCommand command, params object[] args)
        {
            SqlCeParameter p = null;

            switch (commandName)
            {
                case INSERT_CONFIG:
                case UPDATE_CONFIG:

                    if (args.Length != 4)
                        throw new ApplicationException(String.Format("{0} command must have 4 parameters", INSERT_CONFIG));

                    p = new SqlCeParameter("@Plugin", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    p = new SqlCeParameter("@Name", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    p = new SqlCeParameter("@Type", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    p = new SqlCeParameter("@Value", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    break;

                case DELETE_CONFIG:
                case SELECT_CONFIG:

                    if (args.Length != 2)
                        throw new ApplicationException(String.Format("{0} command must have 2 parameters", SELECT_CONFIG));

                    p = new SqlCeParameter("@Plugin", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    p = new SqlCeParameter("@Name", SqlDbType.NVarChar);
                    command.Parameters.Add(p);

                    break;

                default: 
                    break;
            }
        }


        /// <summary>
        /// Create the SqlCeCommand to Insert a config
        /// </summary>
        public static SqlCeCommand GetInsertConfigurationsCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            StringBuilder sbCommand = new StringBuilder();
            sbCommand.AppendLine("Insert into Configurations (Plugin, Name, Type, Value) Values (@Plugin, @Name, @Type, @Value) ");

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sbCommand.ToString();

            return sqlCommand;
        }

        /// <summary>
        /// Create the SqlCeCommand to Update a config
        /// </summary>
        public static SqlCeCommand GetUpdateConfigurationsCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            StringBuilder sbCommand = new StringBuilder();
            sbCommand.AppendLine(" Update Configurations Set Value=@Value, Type=@Type Where Plugin=@Plugin and Name=@Name ");

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sbCommand.ToString();

            return sqlCommand;
        }

        /// <summary>
        /// Create the SqlCeCommand to Delete a config
        /// </summary>
        public static SqlCeCommand GetDeleteConfigurationsCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            StringBuilder sbCommand = new StringBuilder();
            sbCommand.AppendLine(" Delete Configurations Where Plugin=@Plugin and Name=@Name ");

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sbCommand.ToString();

            return sqlCommand;
        }

        private static SqlCeCommand GetSelectAllConfigurationsCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = "Select * from Configurations order by Plugin, Name";

            return sqlCommand;
        }

        private static SqlCeCommand GetSelectConfigurationCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = "Select * from Configurations Where Plugin=@Plugin and Name=@Name";

            return sqlCommand;
        }

        private static SqlCeCommand GetCreateConfigTableCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("CREATE TABLE [Configurations]( ");
	        sb.AppendLine("[Plugin] [nvarchar](100) NOT NULL, ");
	        sb.AppendLine("[Name] [nvarchar](255) NOT NULL, ");
	        sb.AppendLine("[Type] [nvarchar](255) NOT NULL, ");
	        sb.AppendLine("[Value] [nvarchar](255) NOT NULL, ");
            sb.AppendLine("Constraint PK_Plugin_Name Primary Key ([Plugin], [Name])) ");

            sqlCommand.CommandText = sb.ToString();

            return sqlCommand;
        }

        private static SqlCeCommand GetExistConfigTableCommand()
        {
            SqlCeCommand sqlCommand = new SqlCeCommand();

            sqlCommand = new SqlCeCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = "Select count(*) from INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Configurations'";

            return sqlCommand;
        }


        //

    }
}
